package servidor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.StringTokenizer;

public class UtilServidor {

	public static final String DRIVER_CLASS_NAME = "org.sqlite.JDBC";
	public static final String CONNECTION_URL = "jdbc:sqlite:bd2/tren2.s3db";
	//Metodo que devuelve la lista de sensores
	public static LinkedList<Sensor> listenSensor() throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		LinkedList <Sensor> lSensor = new LinkedList<Sensor>();
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		boolean v;
		// Show all the user logins
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM Sensor";
		resultSet = statement.executeQuery(sqlStatementString);
		while(resultSet.next())
		{
		
			String ID = resultSet.getString("ID_Sensor");
			String Des = resultSet.getString("Descripcion");
			String Est = resultSet.getString("Estado");
			String fech = resultSet.getString("Fecha");
			String hor = resultSet.getString("Hora");
			String coor = resultSet.getString("Coordenadas");
			String his = resultSet.getString("Historico");
			int val = resultSet.getInt("Valor");
			String y="false";
			if(Est.equals(y)){
				 v=false;
				}
				else{
					v=true;
					}
				
			
			Sensor s= new Sensor(ID,Des,v,fech,hor,coor,val,his);
			lSensor.addLast(s);
		}
		resultSet.close();
		statement.close();
		
		return lSensor;
		
	
	}
	//Metodo que devuelve el sensor
	public static Sensor sensorTren(int i) throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		Sensor s = null;
		boolean v;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM Sensor WHERE ID_Sensor="+i+"";
		resultSet = statement.executeQuery(sqlStatementString);
		
		
			String ID = resultSet.getString("ID_Sensor");
			String Des = resultSet.getString("Descripcion");
			String Est = resultSet.getString("Estado");
			String fech = resultSet.getString("Fecha");
			String hor = resultSet.getString("Hora");
			String coor = resultSet.getString("Coordenadas");
			String his = resultSet.getString("Historico");
			int val = resultSet.getInt("Valor");
			String y="false";
			if(Est.equals(y)){
				 v=false;
				}
				else{
					v=true;
					}
				
			
			 s= new Sensor(ID,Des,v,fech,hor,coor,val,his);
	
		resultSet.close();
		statement.close();
		
		return s;
		
	
	}
	//Metodo que devuelve el historico
	public static LinkedList<String> listaHistorico (int i) throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		LinkedList <String> lhis = new LinkedList<String>();
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM Sensor WHERE ID_Sensor="+i+"";
		resultSet = statement.executeQuery(sqlStatementString);
		while(resultSet.next())
		{
			String his = resultSet.getString("Historico");
			StringTokenizer tokens=new StringTokenizer(his, "&");
			while(tokens.hasMoreTokens()){
				lhis.addLast(tokens.nextToken());
				}
			
			
		}
		resultSet.close();
		statement.close();
		
		return lhis;
	}
	//devuelve el gps del tren
	public static GPS devolverGPS() throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		GPS s = null;
		boolean v;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM GPS ";
		resultSet = statement.executeQuery(sqlStatementString);
			String Est = resultSet.getString("Estado");
			String val = resultSet.getString("Valor");
			String y="false";
			if(Est.equals(y)){
				 v=false;
				}
				else{
					v=true;
					}
				
			
			 s= new GPS(v,val);
	
		resultSet.close();
		statement.close();
		
		return s;
	}
	//Metodo que devuelve el estado del sensor
	public static boolean estadoSensor (int i) throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		boolean v;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM Sensor WHERE ID_Sensor="+i+"";
		resultSet = statement.executeQuery(sqlStatementString);
		String est = resultSet.getString("Estado");
		String y="false";
		
		if(est.equals(y)){
		 v=false;
		}
		else{
			v=true;
		}
		
		resultSet.close();
		statement.close();
		
		return v;
	}
	//Metodo que devuelve el estado del gps
	public static boolean estadoGps () throws SQLException, ClassNotFoundException{
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM GPS ";
		resultSet = statement.executeQuery(sqlStatementString);
		
			String gps = resultSet.getString("Estado");
			
			String y ="false";
			boolean v;
			if(gps.equals(y)){
			 v=false;
			}
			else{
				v=true;
			}
		resultSet.close();
		statement.close();
		
		return v;
	}
	
	
	
	//Metodo que devuelve el tren
	public static Tren devolverTren() throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM Tren";
		resultSet = statement.executeQuery(sqlStatementString);
		
		
			int ID = resultSet.getInt("ID_Tren");
			String est = resultSet.getString("Estado");
			int ID_hum=resultSet.getInt("ID_Hum");
			int ID_tem=resultSet.getInt("ID_Tem");
			int ID_vel=resultSet.getInt("ID_Vel");
			Sensor hum=sensorTren(ID_hum);
			Sensor tem=sensorTren(ID_tem);
			Sensor vel=sensorTren(ID_vel);
			GPS gps=devolverGPS();
			
			Estado e;
			
			
			
			if(est.toString().equals(Estado.En_mantenimiento.name())){
				
				e=Estado.En_mantenimiento;
			}
			else
			{
				if(est.toString().equals(Estado.Parado.name())){
					
					e=Estado.Parado;
					}
			else
				e=Estado.Funcionando;
			}
			
			
			Tren t= new Tren(ID, gps,e,hum,tem,vel );
		
		resultSet.close();
		statement.close();
	
		return t;
		
	
	}	
	//Metodo que pone a on el sensor
	public static void onSensor(int i) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE Sensor SET Estado='true' WHERE ID_Sensor="+i+"";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//Metodo que pone a off el sensor
	public static void offSensor(int i) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE Sensor SET Estado='false' WHERE ID_Sensor="+i+"";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//Metodo que pone a on el GPS
	public static void onGPS() throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE GPS SET Estado='true'";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//Metodo que pone a off el GPS
	public static void offGPS() throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE GPS SET Estado='false'";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//Metodo devuelve el valor del sensor
	public static String get_valact(int i) throws SQLException, ClassNotFoundException{
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM Sensor WHERE ID_Sensor="+i+"";
		resultSet = statement.executeQuery(sqlStatementString);
		
			String fech = resultSet.getString("Fecha");
			String hor = resultSet.getString("Hora");
			String coor = resultSet.getString("Coordenadas");
			int val = resultSet.getInt("Valor");
		
		String s=fech+" "+hor+" "+coor+" "+val;	
		resultSet.close();
		statement.close();
		
		return s;
	}
	//Metodo devuelve la localizacion del GPS
	public static String get_loc() throws SQLException, ClassNotFoundException{

		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM GPS ";
		resultSet = statement.executeQuery(sqlStatementString);
		
		String gps = resultSet.getString("Valor");
		
		resultSet.close();
		statement.close();
		
		return gps;
	}
	
	
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException
	{
		
	
}}
